package jdbclearn;

import jakarta.servlet.*;
import jakarta.servlet.http.*;
import jakarta.servlet.annotation.*;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

@WebServlet(name = "ServletJDBC02", value = "/ServletJDBC02")
public class ServletJDBC02 extends HttpServlet {

    String databaseName = "EMP";
    String USER = "root";
    String PSD = "123456";

    // Mysql版本8.0及以上
//    String DB_URL = "jdbc:mysql://106.55.150.215:3306/" + databaseName + "?useSSL=false&&allowPublicKeyRetrieval=true&serverTimezone=UTC";
//    String JDBC_DRIVER = "com.mysql.jdbc.Driver";

    // Mysql8.0以下版本 - JDBC驱动名及数据库URL
    static final String DB_URL = "jdbc:mysql://47.111.186.126:3306/EMP";
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName(JDBC_DRIVER);
//            打开连接
            System.out.println("连接数据库......");
            connection = DriverManager.getConnection(DB_URL, USER, PSD);
//            执行查询
            System.out.println("实例化Statement对象...");
            statement = connection.createStatement();
            String sql;
            sql = "SELECT id , first , last, age FROM Employees ";
            ResultSet resultSet = statement.executeQuery(sql);
//            展开结果集数据库
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                int age = resultSet.getInt("age");
                String first = resultSet.getString("first");
                String last = resultSet.getString("last");

                response.setHeader("Content-type", "text/html;charser=UTF-8");
                PrintWriter out = response.getWriter();
//                输出数据
                out.print("ID: " + id + ", 年龄: " + age + ", 姓: " + first + ", 名: " + last + "</br>");
            }
            resultSet.close();
            statement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println("Goodbey!");
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}
